package com.dhcc.DRGsHosp.application.impl;

import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.GroupFunApplication;
@Named
@Transactional
public class GroupFunApplicationImpl implements GroupFunApplication {
	
	@Autowired
	private JdbcTemplateObj jdbcTemplateObj;

	@Override
	public List<Map<String, Object>> findPatientInDatabase(String mrNo,String verRegion) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT B.PMAININDEX patient_id,B.P00050000 mr_no,B.P00060000 name,B.P00070001 sex,B.P00090000 age,  \n");
		sql.append(" I.P00320000 in_days,I.P00800000 cost_sum,I.P00620001 out_way,((case when b.P00090001 is null then 0 else b.P00090001 end)*30+(case when b.P00090002 is null then 0 else b.P00090002 end))as newborn_day,B.P00120000 newborn_w,A.P90120201 ven_hour,D.PD0101 diacode_main,D.PD0100 dia_main,  \n");
		sql.append(" OTHERDIAGNOSIS dia_other,P.MAINOPERATION oper_main,Q.OTHEROPERATION oper_other,DRG.DRG_CODE drg_code,DRG.DRG_DESC drg_desc,DRG.VER_CODE ver_code,to_char(i.P00290000,'yyyy-mm-dd') out_date FROM DRGS.MR_BASEINFO B  \n");
		sql.append(" LEFT JOIN DRGS.MR_IPINFO I ON B.PMAININDEX=I.PMAININDEX   \n");
		sql.append(" LEFT JOIN DRGS.MR_APPENDPAGE A ON B.PMAININDEX=A.PMAININDEX  \n");
		sql.append(" LEFT JOIN DRGS.MR_DIAGNOSE D ON B.PMAININDEX=D.PMAININDEX AND D.PDTYPE='M' \n");
		sql.append(" LEFT JOIN (SELECT D.PMAININDEX, WMSYS.WM_CONCAT(D.PD0101 || '[' || D.PD0100 || ']') OTHERDIAGNOSIS  \n");
		sql.append(" FROM DRGS.MR_DIAGNOSE D  WHERE D.PDTYPE = 'O'GROUP BY D.PMAININDEX) O ON B.PMAININDEX=O.PMAININDEX \n");
		sql.append(" LEFT JOIN (SELECT OP.PMAININDEX,WMSYS.WM_CONCAT(OP.PO0101 || '[' || OP.PO0100 || ']') MAINOPERATION   \n");
		sql.append(" FROM  DRGS.MR_OPERATION OP WHERE OP.POTYPE = 'M' GROUP BY OP.PMAININDEX) P ON B.PMAININDEX=P.PMAININDEX    \n");
		sql.append(" LEFT JOIN (SELECT OP.PMAININDEX,WMSYS.WM_CONCAT(OP.PO0101 || '[' || OP.PO0100 || ']') OTHEROPERATION \n" );
		sql.append(" FROM  DRGS.MR_OPERATION OP WHERE OP.POTYPE = 'O' GROUP BY OP.PMAININDEX) Q ON B.PMAININDEX=Q.PMAININDEX  \n");
		sql.append(" LEFT JOIN DRGs.t_Patientdrg PD ON B.PMAININDEX=PD.PARID  and PD.ver_region='"+verRegion+"' \n");
		sql.append(" LEFT JOIN DRGs.t_Drgs DRG ON PD.PARDRG_CODE=DRG.DRG_CODE AND PD.VER_CODE=DRG.VER_CODE  \n");
		sql.append(" WHERE B.P00050000 IN ('"+mrNo+"')   \n");
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}
	@Override
	public List<Map<String, Object>> findPatientByMrNo(String mrNo,String verRegion){
		StringBuilder sql = new StringBuilder();
		sql.append(" select a.pmainindex case_id, a.P00050000 mr_no, to_char(d.P00290000,'yyyy-mm-dd') out_date, a.P00090000 AGE, d.p00320000 ACCTUAL_DAYS, P00070001 GENDER, d.p00620001 sf0108,f.pd0101 DISEASE_CODE, \n ");
		sql.append(" d.p00800000 TOTAL_EXPENSE,  nvl(a.P00090001*30,0)+a.p00090002 sf0100, a.P00120000 sf0102,\n ");
		sql.append(" e.p90120201 sf0104, b.diags, c.opers, substr(cast(dbms_random.value as varchar2(10)),3,6) B_WT4_V1_ID,DRG.DRG_CODE,DRG.DRG_DESC \n ");
		sql.append(" from drgs.mr_baseinfo a\n ");
		sql.append(" left join drgs.mr_ipinfo d on a.pmainindex=d.pmainindex\n ");
		sql.append(" left join drgs.mr_appendpage e on a.pmainindex=e.pmainindex\n ");
		sql.append(" left join drgs.mr_diagnose f on a.pmainindex=f.pmainindex and f.pdtype='M'\n ");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON a.PMAININDEX=P.PARID  and P.ver_region='"+verRegion+"' \n");
		sql.append(" LEFT JOIN DRGS.T_DRGS DRG ON P.PARDRG_CODE=DRG.DRG_CODE AND P.VER_CODE=DRG.VER_CODE  \n");
		sql.append(" left join (\n ");
		sql.append(" select parid, wm_concat(diag_code) diags from(\n ");
		sql.append(" select pmainindex parid, a.pd0101 diag_code from drgs.mr_diagnose a \n ");
		sql.append(" where a.pdtype='O'\n ");
		sql.append(" )\n ");
		sql.append(" group by parid\n ");
		sql.append(" ) b on a.pmainindex=b.parid\n ");
		sql.append(" left join (\n ");
		sql.append(" select parid, wm_concat(oper_code) opers from(\n ");
		sql.append(" select pmainindex parid, a.po0101 oper_code \n ");
		sql.append(" from drgs.mr_operation a\n ");
		sql.append(" )\n ");
		sql.append(" group by parid\n ");
		sql.append(" ) c on a.pmainindex=c.parid\n ");
		sql.append(" where a.P00050000 IN ('"+mrNo+"') \n ");
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		
		return resultList;
	}
}
